package test2;

import org.json.JSONObject;

import java.sql.*;

public class Main {
    public static void main(String[] args) {
        try {
            // 连接到数据库
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/lindashixi?serverTimezone=GMT", "root", "220724");

            // 创建查询语句
            String query = "SELECT p.name, d.name as department, p.id as person_id, d.id as department_id, p.salary " +
                    "FROM employee p " +
                    "JOIN department d ON p.departmentId = d.id";

            // 执行查询
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);

            // 创建 JSON 对象
            JSONObject data = new JSONObject();

            // 处理查询结果
            while (resultSet.next()) {
                // 获取每一行的数据
                String personName = resultSet.getString("name");
                String departmentName = resultSet.getString("department");
                int personId = resultSet.getInt("person_id");
                int departmentId = resultSet.getInt("department_id");
                double salary = resultSet.getDouble("salary");

                // 构建结果字符串，以满足指定的结构
                String key = personName + "-" + departmentName;
                String value = "[" + personId + ", " + departmentId + ", " + salary + "]";

                // 将数据添加到 JSON 对象
                data.put(key, value);
            }

            // 创建最终的 JSON 输出
            JSONObject output = new JSONObject();
            output.put("data", data);

            // 打印 JSON 输出
            System.out.println(output);

            // 关闭连接
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}